<?php
namespace GRH\Model\Table;

use Zend\Db\TableGateway\AbstractTableGateway;
use Zend\Db\Adapter\Adapter;
use Zend\Db\ResultSet\ResultSet;

use Zend\Db\Sql\Sql;
use Zend\Db\Sql\Where;
use Zend\Db\Sql\Expression;
use Zend\Db\Sql\Select;

use GRH\Model\Periode;
use GRH\Helper\DateTime;

class PeriodeTable extends AbstractTableGateway
{
    protected $table = 'grh_periode';
    
    /**
     * Constructeur
     * 
     * @param \Zend\Db\Adapter\Adapter $adapter
     */
    public function __construct(Adapter $adapter)
    {
        $this->adapter = $adapter;
        $this->resultSetPrototype = new ResultSet();
        $this->resultSetPrototype->setArrayObjectPrototype(new Periode());
        $this->initialize();
    }
    
    /**
     * Tous les périodes sous forme de collection d'objet
     * 
     * @return Collection
     */
    public function getAll()
    {
        return $this->select();
    }
    
    /**
     * Tous les périodes pour une utilisation dans une zone de liste déroulante
     * 
     * @return array
     */
    public function getSelectList()
    {
        $rows = $this->select();
        $arr = array();
        foreach($rows as $periode)
            $arr[$periode->periode_id] = html_entity_decode($periode->libelle);
        
        return $arr;
    }
    
    /**
     * Récupérer une période identifié par son ID
     * 
     * @param int $id
     * @return GRH\Model\Periode
     * @throws \Exception
     */
    public function get($id)
    {
        $id = (int)$id;
        $row = $this->select(array('periode_id' => $id))->current();
        if(!$row)
            throw new \Exception("PeriodeTable::get => Impossible de trouver la période portant l'id = $id");
        
        return $row;
    }
    
    /**
     * Récupérer la valeur maximale de la période
     * 
     * @return type
     * @throws \Exception
     */
    public function getMaxValue()
    {
        $sql = new Sql($this->adapter);
        $select = $sql->select();
        $select->from($this->table);
        $select->columns(array(
            'max' => new Expression("MAX(periode_id)"),
        ));
        $statement = $sql->prepareStatementForSqlObject($select);
        $result = $statement->execute()->current();
        
        return (int)$result['max'];
    }
    
    /**
     * Récupération de la liste des prochains Lundi
     * 
     * @return type
     */
    public function getNextMonday()
    {
        $sql = new Sql($this->adapter);
        $select = $sql->select();
        $select->from($this->table);
        $select->columns(array(
            'next_monday' => new Expression('DATE_ADD(MAX(fin_periode), INTERVAL 1 DAY)'),
        ));
        $statement = $sql->prepareStatementForSqlObject($select);
        $result = $statement->execute();
        $count = $result->count();
        //var_dump($count);
        $current = $result->current();
        if($count < 1 || $current['next_monday'] == '' || is_null($current['next_monday'])){
            $now = date('Y-m-d');
            $date_fr = DateTime::date_fr(strtotime($now));
            $date = explode(' ', $date_fr);
            $first_lundi = array();
            if(strtolower($date[0]) == 'lundi'){
                $first_lundi[$now] = $date_fr;
            }
            else{
                $interval = \DateInterval::createFromDateString('next monday');
                $begin = new \DateTime();
                $begin->modify('next monday');
                $end = new \DateTime();
                $end->modify('last day of this month');
                $period = new \DatePeriod($begin, $interval, $end);
                foreach($period as $date)
                {
                    $first_lundi[$date->format('Y-m-d')] = html_entity_decode(DateTime::date_fr(strtotime($date->format('Y-m-d'))));
                }
            }
             return $first_lundi; 
        }
        else{
            return array(
                $current['next_monday'] => html_entity_decode(DateTime::date_fr(strtotime($current['next_monday']))),
            );
        }
    }
    
    /**
     * Récupération des prochains Dimanche
     * 
     * @param string $monday
     * @param int $count
     * @return array
     */
    public function getNextSunday($monday, $count = 5)
    {
        $begin = new \DateTime($monday);
        $end = clone $begin;
        $count = $count * 7;
        $end->modify("+ $count days");
        $begin->modify('next sunday');
        $interval = \DateInterval::createFromDateString('next sunday');
        $periode = new \DatePeriod($begin, $interval, $end);
        $sundays = array();
        $number = 1;
        foreach($periode as $date)
        {
            $sundays[$date->format('Y-m-d')] = $number++ . ' - ' .html_entity_decode(DateTime::date_fr(strtotime($date->format('Y-m-d'))));
        }
        
        return $sundays;
    }
    
    /**
     * Récupérer le prochain nom de période
     * 
     * @return string
     */
    public function getNextLabel()
    {
        $sql = new Sql($this->adapter);
        $select = $sql->select();
        $select->from($this->table);
        $select->columns(array(
            'last_label' => new Expression('MAX(libelle)'),
        ));
        $statement = $sql->prepareStatementForSqlObject($select);
        $result = $statement->execute();
        $current = $result->current();
        if($result->count() == 0 || $current['last_label'] == '' || is_null($current['last_label']))
        {
            return 'Période 1';
        }
        else
        {
            $last_label = $current['last_label'];
            //$last_label = $last_label['last_label'];
            //var_dump($last_label);
            $number = substr($last_label, 8);
            //var_dump($number);
            $number = (int)$number;
            if($number == 0) $number = 1;
            //var_dump($number);
            return 'Période ' . ++$number;
        }
    }

    /**
     * Détermine si une date est comprise entre les 2 dates
     * 
     * @param type $date
     * @param \Datetime $debut
     * @param \Datetime $fin
     * @return boolean
     */
    public function isbetween($date, $debut, $fin)
    {
        $return = false;
        $debut = new  \Datetime($debut);
        $fin = new \Datetime($fin);
        $now = new \Datetime();
        $intervale1 = $debut->diff($date);
        $intervale2 = $fin->diff($date);
        if($intervale1->d > 0 && $intervale2->d <= 0) return true;
        else return false;
    }
    
    /**
     * Sauvegarder une Période dans la base de données
     * 
     * @param \GRH\Model\Periode $periode
     * @return int L'identifiant de la dernière valeur enregistrée dans la base de données
     * @throws \Exception
     */
    public function save(Periode $periode)
    {
        $data = array(
            'libelle'       => $periode->libelle,
            'debut_periode' => $periode->debut_periode,
            'fin_periode'   => $periode->fin_periode,
        );
        $id = (int)$periode->periode_id;
        if($id == 0){
            $this->insert($data);
        }
        else{
            if($this->get($id))
                $this->update ($data, array('periode_id' => $id));
            else
                throw new \Exception("Impossible de trouver '$id'");
        }
        if($id == 0) return $this->lastInsertValue;
        else return $id;
    }
    
    /**
     * Effacer une période
     * 
     * @param type $id
     * @return int
     */
    public function delete($id)
    {
        if($this->delete(array('periode_id' => $id)))
            return (int)$id;
        else
            return 0;
    }
}